package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created with IntelliJ IDEA.
 * Description:
 * model.User: DELL
 * Date: 2023-03-22
 * Time: 17:24
 */

// 通过该类封装博客表中的基本操作

public class BlogDao {

    // 1. 新增一个博客
    public void add(Blog blog) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            // 建立连接
            connection = DBUtil.getConnection();
            // 构造 sql
            String sql = "insert into blog values(null, ?, ? ,now(), ?)";
            statement = connection.prepareStatement(sql);
            // 替换占位符 TODO: 占位符从 1 开始
            statement.setString(1, blog.getTitle());
            statement.setString(2, blog.getContent());
            statement.setInt(3, blog.getUserId());

            // 执行 sql
            statement.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(connection, statement, null);
        }
    }

    // 2. 根据博客 id 来查询指定博客(博客详情页中)
    public Blog selectById(int blogId) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        // blogId 在 blog 中为自增主键唯一的
        try {
            // 1. 建立连接
            connection = DBUtil.getConnection();
            // 2. 构建 sql
            String sql = "select * from blog where blogId = ? ";
            statement = connection.prepareStatement(sql); // 执行 sql 语句
            statement.setInt(1, blogId);
            // 3. 执行 sql 语句
            resultSet = statement.executeQuery();
            // 4. 遍历 resultSet 集合. 由于 blog 表中 blogId 是唯一的(自增主键)
            //        此时的查询结果要么是没有 要么是只有一条记录
            //        此时可以不是用 while 直接是用 if 判断
            if (resultSet.next()) {
                Blog blog = new Blog();
                // 获取到当前 blog 对象中的所有值并设置到 blog 对象中
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                blog.setContent(resultSet.getString("content"));
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                return blog;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return null;
    }

    // 3. 直接查询数据中所有博客列表(用于博客列表页)
    public List<Blog> selectAll() {
        List<Blog> blogs = new ArrayList<>();
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;

        try {
            // 1. 建立连接
            connection = DBUtil.getConnection();
            // 2. 构造 sql
            String sql = "select * from blog order by postTime desc";
            statement = connection.prepareStatement(sql);
            // 3. 执行 sql
            resultSet = statement.executeQuery();
            // 4. 遍历结果集合
            while (resultSet.next()) {
                Blog blog = new Blog();
                // 获取到当前 blog 对象中的所有值并设置到 blog 对象中
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                // 博客列表页中, 正文应该展示的是摘要, 整个内容应当展示到博客详情页面中
                // 需要对 content 进行截取
                String content = resultSet.getString("content");
                if (content.length() >= 100) {
                    // substring(index, end) 返回该下标内的字符串(左闭右开)
                    content = content.substring(0, 100) + "...";
                }
                // 把是否截取后的内容设置回去
                blog.setContent(content);
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                blogs.add(blog);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return blogs;
    }

    // 4. 删除指定博客
    public void delete(int blogId) {
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            // 1. 建立连接
            connection = DBUtil.getConnection();
            // 2. 构造 sql
            String sql = "delete from blog where blogId = ?";
            statement = connection.prepareStatement(sql);
            // 设置 sql 语句中占位符
            statement.setInt(1, blogId);
            // 3. 执行 sql
            statement.executeUpdate();

        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 4. 关闭资源
            DBUtil.close(connection, statement, null);
        }
    }

    public int countBlogByUserId(int userId) {
        int count = 0;
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        try {
            // 1. 建立连接
            connection = DBUtil.getConnection();
            // 2. 构造 sql
            String sql = "select count(*) as total from blog where userId = ?";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userId);
            // 3. 执行 sql
            resultSet = statement.executeQuery();
            // 4. 遍历结果结合
            resultSet.next();
            count = resultSet.getInt("total");
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }
        return count;
    }

    public List<Blog> selectBlogByUserId(int userId) {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<Blog> blogs = new ArrayList<>();
        try {
            connection = DBUtil.getConnection();
            String sql = "select * from blog where userId = ? order by postTime desc";
            statement = connection.prepareStatement(sql);
            statement.setInt(1, userId);
            resultSet = statement.executeQuery();
            while(resultSet.next()) {
                // 遍历结果集合放到 blog 中返回去
                Blog blog = new Blog();
                blog.setBlogId(resultSet.getInt("blogId"));
                blog.setTitle(resultSet.getString("title"));
                String content = resultSet.getString("content");
                if (content.length() >= 100) {
                    // substring(index, end) 返回该下标内的字符串(左闭右开)
                    content = content.substring(0, 100) + "...";
                }
                // 把是否截取后的内容设置回去
                blog.setContent(content);
                blog.setPostTime(resultSet.getTimestamp("postTime"));
                blog.setUserId(resultSet.getInt("userId"));
                blogs.add(blog);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtil.close(connection, statement, resultSet);
        }

        return blogs;
    }
}
